import os
from . import upfile
from flask import request,jsonify
from .handle_api import parse_studentdata,parse_characterdata,parse_achievementdata,parse_bookdata
from app.setting import DevConfig
from app.model import zhaoshengStudentData,bookStudentData,characterData,achievementData,db
from sqlalchemy.sql.functions import count
CURRENT = os.path.dirname(os.path.dirname(__file__))

# 增加目录后面加多一个 /
UPLOAD_PATH = CURRENT + '/api/updata/'

HANDLE_PATH = CURRENT + '/api/handle/'

engine=DevConfig.engine

#可以选择上传的文件
def allowed_file(filename):
    ALLOWED_EXTENSIONS = ['xls', 'xlsx']
    return '.' in filename and filename.rsplit('.', 1)[1] in ALLOWED_EXTENSIONS

def getuploadpath(filename):
    uploadfile ="{}".format(filename)
    datafile = "{}.csv".format(filename)
    return os.path.join(UPLOAD_PATH ,uploadfile), os.path.join(HANDLE_PATH, datafile)

# 查询数据库中的记录
@upfile.route("/infos")
def infos():
    years=zhaoshengStudentData.query.with_entities(zhaoshengStudentData.year,count().label("count2")).group_by(zhaoshengStudentData.year).all()
    print(years)
    studentData=[]
    for year in years:
        studentData.append({
            'year':year.year,
            'count':year.count2
        })
    print(studentData)
    bookData_count = bookStudentData.query.count()
    print(bookData_count)
    characterData_count = characterData.query.count()
    achievementData_count =  achievementData.query.count()

    return jsonify({
        'studentData':studentData,
        'bookData':bookData_count,
        'characterData':characterData_count,
        'achievementData':achievementData_count
    })

@upfile.route('/updata',methods=['POST'])
def updata():

    file = request.files['file']
    if not allowed_file(file.filename):
        return jsonify({
            'code':1,
            'msg':'文件格式错误，只支持xls或者xlsx格式的Excel文件'
        })

    uploadType = request.form['uploadType']
    outpath = os.path.join(UPLOAD_PATH,uploadType+".xls")
    print("保存输出文件:")
    print(outpath)
    # 保存输出文件
    file.save(outpath)
    
    msg="解析成功"
    if uploadType=='studentData':
        msg=parse_studentdata(outpath)
    elif uploadType=='characterData':
        msg=parse_characterdata(outpath)
    elif uploadType=='bookData':
        msg=parse_bookdata(outpath)
    elif uploadType=='achievementData':
        msg = parse_achievementdata(outpath)
    print(msg)
    return jsonify(msg)

# 删除数据
@upfile.route('/delete',methods=['POST'])
def deletefile():
    pass
    uploadType = request.json['uploadType']

    if uploadType=='studentData':
        # 删除学生数据需要指定年份
        year=request.json['year']
        zhaoshengStudentData.query.filter(zhaoshengStudentData.year==year).delete()
        db.session.commit()
        pass
    elif uploadType=='characterData':
        characterData.query.delete()
        db.session.commit()
        pass
    elif uploadType=='bookData':
        bookStudentData.query.delete()
        db.session.commit()
    elif uploadType=='achievementData':
        achievementData.query.delete()
        db.session.commit()
    return "删除成功"


# tojson sqlalchemy结果转换为字典
def todict(item):
    result={}
    for attr in dir(item):
        if attr.startswith("_"):
            continue
        if attr=='metadata' or attr=='query' or attr=='query_class' or attr=='id':
            continue
        result[attr]=getattr(item,attr)
    return result
    
# 获取预览数据
@upfile.route('/preview',methods=['POST'])
def preview():
    uploadType = request.json['uploadType']
    if uploadType=='studentData':
        result = zhaoshengStudentData.query.limit(100)
    elif uploadType=='characterData':
        result = characterData.query.limit(100)

    elif uploadType=='bookData':
        result = bookStudentData.query.limit(100)

    elif uploadType=='achievementData':
        result = achievementData.query.limit(100)
    result=result.all()
    print(result)
    preview_columns=[]
    preview_data = []
    for item in result:
        item_dict = todict(item)
        preview_columns = list(item_dict.keys())
        preview_data.append(item_dict)
    return {
        'preview_data':preview_data,
        'preview_columns':preview_columns
    }

 



@upfile.route('/upload',methods=['POST'])
def upload():
    print("上传数据，参数",request.files,request.form)
    if request.method == 'POST':
        # 上传的url requests.post('http://127.0.0.1:5000/api/upfile/upload',data={"year":2018},files=files)
        #这里可以添加上传参数  data 为上传的参数。
        #formdata = request.form
        if 'file' not in request.files:
            return jsonify({'code': -1, 'filename': '', 'msg': '没有指定文件file参数'})
        # 如果用户没有选择文件，浏览器也会提交一个没有文件名的空零件
        file = request.files['file']
        if file.filename == '':
            return jsonify({'code': -1, 'filename': '', 'msg': '没有选择文件'})
        else:
            pass
            try:
                if file and allowed_file(file.filename):
                    origin_file_name = file.filename
                    filename = origin_file_name
                    if os.path.exists(UPLOAD_PATH):
                        pass
                    else:
                        os.makedirs(UPLOAD_PATH)
                    uploadfile,datafile=getuploadpath(filename)

                    print("保存到:",uploadfile)
                    # 文件保存路径以及文件名
                    file.save(uploadfile)

                    return jsonify({'code': 0, 'filename': origin_file_name, 'msg': 'Success'})

                else:
                    return jsonify({'code': -1, 'filename': '', 'msg': 'File not allowed'})


            except Exception as e:
                print(e)
                return jsonify({'code': -1, 'filename': '', 'msg': 'Error occurred'})


    else:
        return jsonify({'code': -1, 'filename': '', 'msg': 'Method not allowed'})






